Note: Before you read this SAS note, refer to SAS note 40834 "The Replication Wizard in SAS® 9.2 has been deprecated". It provides important information that might affect your future processing.

An error occurs when you perform manual substitutions for paths in the promotion job and then run the promotion job or replication jobs in SAS Management Console:

Required value not in Substitution table: inTarg=1 inSubs=0

Manual substitutions are not saved following a successful validation.

To resolve this issue, use the %MACRO MDVAL autocall macro. See the Full Code tab above for sample code of the %MACRO MDVAL autocall macro. This macro includes the following lines:

fromValue=htmldecode(fromValue); 
toValue=htmldecode(toValue);

This change requires each value to be decoded in the substitution table so the macro can compare correctly against the actual SAS metadata object values that are being updated. Then the macro re-encodes the values during the UpdateMetadata CALL routine in order to apply substitutions to the target repository.

Operating System and Release Information

This macro is used by SAS Management Console to validate substitution tables that are defined for a repository promotion.

Note: To use the macro, create a job in SAS Management Console by going to the Metadata Manager, selecting the Job Definitions category and choosing "New Job..." from the context menu on the Replication or Promotion nodes.

%macro mdval(host, port, user, pw, prot,
             reposName, reposID, jobReposName, jobID);
%*********************************************************************;
%*                                                                   *;
%*  MACRO: MDVAL                                                     *;
%*                                                                   *;
%*  USAGE: 1) MDVAL(reposName, reposID, jobReposName, jobID)         *;
%*                                                                   *;
%*  DESCRIPTION:                                                     *;
%*    This macro is used by SAS Management Console to validate       *;
%*    substitution tables defined for a repository promotion.        *;
%*                                                                   *;
%*    The host parameter takes the host name of the metadata server. *;
%*    The port parameter takes the port of the metadata server.      *;
%*    The user parameter takes the user to connect with.             *;
%*    The pw parameter takes the password to connect with.           *;
%*    The prot parameter takes the protocol to connect with.         *;
%*    The reposName parameter takes the name of the repository to be *;
%*        replicated.                                                *;
%*    The reposID parameter takes the metadata ID of the repository  *;
%*        to be replicated.                                          *;
%*    The jobReposName parameter takes the name of the Job repos.    *;
%*    The jobID parameter takes the metadata ID of the Job.          *;
%*                                                                   *;
%*    To use the macro, create a job in SAS Management Console       *;
%*    by going to the Metadata Manager, selecting the Job Definitions*;
%*    category and choosing "New Job..." from the context menu on the*;
%*    Replication or Promotion nodes.                                *;
%*********************************************************************;
%put Validating Substitution Tables;
%put host="&host" port="&port" user="&user";
%put reposName="&reposName" reposID="&reposID";
%put jobReposName="&jobReposName" jobID="&jobID";

%global mdsubsPromoError;
%global mdsubsHostsUnused mdsubsPathsUnused
        mdsubsPortsUnused mdsubsSchemasUnused;
%let mdsubsHostsUnused=0;
%let mdsubsPathsUnused=0;
%let mdsubsPortsUnused=0;
%let mdsubsSchemasUnused=0;


   /* Define the location for updateMetadata XML to be written and prime it. */
filename updtMeta temp;
data _null_;
file updtMeta;
put '';
put '  $METAREPOSITORY';
put '  ';
run;


   /* Get all the substitution tables from the job.*/
%mdsubsGetTbls(Hosts,&jobReposName,SMCHostSubTable,&jobID)
%mdsubsGetTbls(Paths,&jobReposName,SMCPathSubTable,&jobID)
%mdsubsGetTbls(Ports,&jobReposName,SMCPortSubTable,&jobID)
%mdsubsGetTbls(Schemas,&jobReposName,SMCSchemaSubTable,&jobID)

   /* Get the host names to modify and build updateMetadata statements. */
%if ^&mdsubsHostsUnused %then %do;
  %mdsubsPrepUpdates(Hosts,&reposName,SASClientConnection,
                     Id,17,Name,200,RemoteAddress,512)
  %mdsubsPrepUpdates(Hosts,&reposName,COMConnection,
                     Id,17,Name,200,HostName,512)
  %mdsubsPrepUpdates(Hosts,&reposName,TCPIPConnection,
                     Id,17,Name,200,HostName,512)
  %mdsubsPrepUpdates(Hosts,&reposName,Machine,
                     Id,17,Desc,200,Name,512)
%end;

   /* Get the paths to modify and build updateMetadata statements. */
%if ^&mdsubsPathsUnused %then %do;
  %mdsubsPrepUpdates(Paths,&reposName,ArchiveEntry,
                     Id,17,Name,200,Path,512)
  %mdsubsPrepUpdates(Paths,&reposName,ArchiveFile,
                     Id,17,Name,200,FileName,512)
  %mdsubsPrepUpdates(Paths,&reposName,Directory,
                     Id,17,Name,200,DirectoryName,512)
  %mdsubsPrepUpdates(Paths,&reposName,Document,
                     Id,17,Name,200,URI,512)
  %mdsubsPrepUpdates(Paths,&reposName,File,
                     Id,17,Name,200,FileName,512)
  %mdsubsPrepUpdates(Paths,&reposName,ITChannel,
                     Id,17,Name,200,URL,512)
  %mdsubsPrepUpdates(Paths,&reposName,ITContentSubscriber,
                     Id,17,Name,200,URL,512)
  %mdsubsPrepUpdates(Paths,&reposName,ITEventSubscriber,
                     Id,17,Name,200,URL,512)
  %mdsubsPrepUpdates(Paths,&reposName,ITSubscriber,
                     Id,17,Name,200,URL,512)
%end;

   /* Get the ports to modify and build updateMetadata statements. */
%if ^&mdsubsPortsUnused %then %do;
  %mdsubsPrepUpdates(Ports,&reposName,SASClientConnection,
                     Id,17,Name,200,Port,512)
  %mdsubsPrepUpdates(Ports,&reposName,TCPIPConnection,
                     Id,17,Name,200,Port,512)
%end;

   /* Get the schema names to modify and build updateMetadata statements. */
%if ^&mdsubsSchemasUnused %then %do;
  %mdsubsPrepUpdates(Schemas,&reposName,DatabaseSchema,
                     Id,17,Name,200,SchemaName,512)
%end;

   /* Update the repository information.  First close out updateMetadata xml. */
data _null_;
file updtMeta mod;
put '  ';
put '  ';
put '  SAS';
put '  268435456';
put '  ';
put '';
run;

   /* Dump out the updateMetadata statements for debugging purposes. */
%if &debug %then %do;
data _null_;
infile updtMeta;
input;
put _infile_;
run;
%end;

%mend mdval;

%macro mdsubsGetTbls(subsType, jobReposName, role, jobId);
%*********************************************************************;
%*                                                                   *;
%*  MACRO: mdsubsGetTbls                                             *;
%*                                                                   *;
%*  USAGE: 1) mdsubsGetTbls(subsType, jobReposName, role, jobID)     *;
%*                                                                   *;
%*  DESCRIPTION:                                                     *;
%*    This macro is used internally by the mdsubs autocall macro to  *;
%*    acquire the substitution values needed in a promotion job.     *;
%*                                                                   *;
%*    The subsType parameter takes the type of substitution value.   *;
%*    The jobReposName parameter takes the name of the repository    *;
%*        that contains the promotion job definition and the         *;
%*        substitution table to be used.                             *;
%*    The role parameter takes the role value in the metadata for    *;
%*        this particular kind of substitution table.                *;
%*    The jobID parameter takes the metadata ID of the Job.          *;
%*                                                                   *;
%*********************************************************************;

%global mdsubs&subsType.Unused;

   /* Create the XML map that is needed to acquire the substitution table. */
filename subsMap temp;

data _null_;
file subsMap;
put '';
put '';
put '  ';
put '    //Objects/TextStore';
put '    ';
put '      //Objects/TextStore@Name';
put '      character';
put '      STRING';
put '      200';
put '      ';
put '    ';
put '      //Objects/TextStore@TextType';
put '      character';
put '      STRING';
put '      16';
put '      ';
put '    ';
put '      //Objects/TextStore@StoredText';
put '      character';
put '      STRING';
put '      32767';
put '      ';
put '    ';
put '  ';
run;

   /* Create the XML statements that are needed to acquire the subsitution table. */
filename getMdO temp;

data _null_;
file getMdO;
put '';
put '  $METAREPOSITORY';
put '  TextStore';
put '  SAS';
put '  268435844';
put '  ';
put '    ';
put '      ';
put '        ';
put '      ';
put "    ";
put '    ';
put '';
put ;
run;

   /* Get the substitution table for this job from the metadata server. */
filename rsltsMdO temp;

proc metadata
  /* Note: no server:port specified because we use the one provided by */
  /* %mdreplsu at the beginning of %mdrepl.                            */
  /* server="&host" port=&port protocol=&prot                          */
  /* userid="&user" password="&pw"                                     */
  repository="&jobReposName"
  in=getMdO
  out=rsltsMdO
  verbose;
run;

   /* Read the substitution table using the XML map.  Note that the substitution */
   /* table is defined as XML itself.  You are parsing that explicitly below     */
   /* because it is simpler than writing it back out to read in again via the    */
   /* XML engine.                                                                */
libname rsltsMdO xml xmlmap=subsMap;

data work.&subsType(keep=Type TextType attributeName fromValue toValue);
  length searchString $200 fromValue $512 toValue $512;
  set rsltsMdO.SubsTbl;
  %if &debug %then %do;
  put Type= TextType= StoredText= ;
  %end;
  if ('UNUSED' = upcase(TextType)) then
    call symput("mdsubs&subsType.Unused",'1');
  searchString="FromValue=";
  searchLength=length(searchString);
  startPos=indexw(StoredText,searchString,'" ')+searchLength;
  fromValue=substr(StoredText,startPos+1);
  fromValueLen=indexc(fromValue,'"')-1;
  if fromValueLen<1 then fromValue=' ';
  else fromValue=substr(fromValue,1,fromValueLen);
  fromValue=htmldecode(fromValue);
  searchString="ToValue=";
  searchLength=length(searchString);
  startPos=indexw(StoredText,searchString,'" ')+searchLength;
  toValue=substr(StoredText,startPos+1);
  toValueLen=indexc(toValue,'"')-1;
  if toValueLen<1 then toValue=' ';
  else toValue=substr(toValue,1,toValueLen);
  toValue=htmldecode(toValue);
  searchString="AttributeName=";
  searchLength=length(searchString);
  startPos=indexw(StoredText,searchString,'" ')+searchLength;
  attributeName=substr(StoredText,startPos+1);
  attributeNameLen=indexc(attributeName,'"')-1;
  if attributeNameLen<1 then attributeName=' ';
  else attributeName=substr(attributeName,1,attributeNameLen);
run;

   /* Sort the substitution table by Type and fromValue for a subsequent merge. */
proc sort; by Type fromValue; run;

   /* Dump the substitution table if you are debugging. */
%if &debug %then %do;
title "Substitution Table - &subsType";
proc print; run;
%end;

%mend mdsubsGetTbls;


%macro mdsubsDefCol(objType, colName, maxLen);
%*********************************************************************;
%*                                                                   *;
%*  MACRO: mdsubsDefCol                                              *;
%*                                                                   *;
%*  USAGE: 1) mdsubsDefCol(objType, colName, maxLen)                 *;
%*                                                                   *;
%*  DESCRIPTION:                                                     *;
%*    This macro is used internally by the mdsubs autocall macro to  *;
%*    define a column specification for the xml engine.  It is used  *;
%*    to generate datastep statements within mdsubsPrepUpdates.      *;
%*                                                                   *;
%*    The objType parameter takes the type of object to work with.   *;
%*    The colName parameter takes the name of the column to create.  *;
%*    The maxLen parameter takes the maximum column length value.    *;
%*                                                                   *;
%*********************************************************************;
put "    ";
put "      //Objects/&objType@&colName";
put '      character';
put '      STRING';
put "      &maxLen";
put '      ';
%mend mdsubsDefCol;


%macro mdsubsPrepUpdates(subsType, reposName, objType, col1Name, col1Len,
                         col2Name, col2Len, col3Name, col3Len);
%*********************************************************************;
%*                                                                   *;
%*  MACRO: mdsubsPrepUpdates                                         *;
%*                                                                   *;
%*  USAGE: 1) mdsubsPrepUpdates(subsType, reposName, objType,        *;
%*        col1Name, col1Len, col2Name, col2Len, col3Name, col3Len)   *;
%*                                                                   *;
%*  DESCRIPTION:                                                     *;
%*    This macro is used internally by the mdsubs autocall macro to  *;
%*    prepare get the specific metadata values that are to be        *;
%*    replaced and to merge those with the substitution values and   *;
%*    finally to prepare the UpdateMetadata statements.              *;
%*                                                                   *;
%*    The subsType parameter takes the type of substitution value.   *;
%*    The reposName parameter takes the name of the repository that  *;
%*        contains the destination repository to modify.             *;
%*        table to be used.                                          *;
%*    The objType parameter takes the type of object to work with.   *;
%*    The col*Name parameter takes the name of the column to create. *;
%*    The col*Len parameter takes the maximum column length value.   *;
%*                                                                   *;
%*********************************************************************;

%global mdsubsPromoError;

   /* Create the XML map that is needed to acquire the target values. */
filename targMap temp;

data _null_;
file targMap;
put '';
put '';
put "  ";
put "    //Objects/&objType";
put '    ';
put "      //Objects/&objType";
put '      character';
put "      &objType";
put '      STRING';
put '      200';
put '      ';
%mdsubsDefCol(&objType,&col1Name,&col1Len)
%mdsubsDefCol(&objType,&col2Name,&col2Len)
%mdsubsDefCol(&objType,&col3Name,&col3Len)
put '    ';
put '  ';
run;

   /* Create the XML statements that are needed to acquire the target values. */
filename getMdO temp;

data _null_;
file getMdO;
put '';
put '  $METAREPOSITORY';
put "  &objType";
put '  ';
put '  SAS';
put '  268435720';
put '  ';
put '  ';
put ;
run;

   /* Get the target values from the metadata server. */
filename rsltsMdO temp;

proc metadata
  server="&host" port=&port protocol=&prot
  userid="&user" password="&pw"
  repository="&reposName"
  in=getMdO
  out=rsltsMdO
  verbose;
run;

   /* Read the target values using the XML map. */
libname rsltsMdO xml xmlmap=targMap;

data work.&subsType&objType;
  set rsltsMdO.&subsType&objType;
run;

   /* Sort the target values table by Type and col3 for a subsequent merge. */
proc sort; by Type &col3Name; run;

   /* Dump the target values table if you are debugging. */
%if &debug %then %do;
title "Target Values - &subsType - &objType";
proc print; run;
%end;

   /* Do a match merge of the substitution table and the target values   */
   /* so that you can get the toValue associated with the target values. */
data work.new&subsType&objType;
merge &subsType&objType(in=inTarg)
      &subsType(rename=(fromValue=&col3Name) in=inSubs);
        by Type &col3Name;
   /* If the orignal value is in both locations and it was modified by */
   /* the user in the substitution table, then write it out.           */
if (inTarg and inSubs) then do;
  if ('...ENTER VALUE...'=upcase(toValue)) then do;
    put "Error: substitution value not provided: " _all_;
    call symput('mdsubsPromoError','1');
    stop;
  end;
  else output;
end;
   /* If the original value is in the target values but is not defined */
   /* in the substitution table, then flag this as an error.           */
else if (inTarg and ^inSubs) then do;
  put "Error: Required value not in Substitution table: " _all_;
  call symput('mdsubsPromoError','1');
  stop;
end;
   /* Do not do anything special for extras in the substitution table. */
else if (^inTarg and inSubs) then do;
  put "Note: extra Substitution value found: " Type= &col3Name= toValue= /* _all_ */;
end;

run;

   /* Dump the modified values table if we are debugging. */
%if &debug %then %do;
title "Modified Values - &subsType - &objType";
proc print; run;
%end;

   /* Add the needed commands to the UpdateMetadata command file for */
   /* use in the final update.                                       */
data _null_;
file updtMeta mod;
length str $1024;
set new&subsType&objType;
toValue=htmlencode(toValue, "amp lt gt apos quot");
str="    <&objType Id=""" || trim(Id) || """ &col3Name=""" || trim(toValue) || '">';
put @6 str;
put "      ";
%mend mdsubsPrepUpdates;